//
//  DataBaseHandler.m
//  数据库来一波
//
//  Created by silently on 15-2-4.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataBaseHandler.h"

@implementation DataBaseHandler
+ (DataBaseHandler *)shareInstance
{
    static DataBaseHandler *dataHandler = nil;
    if (dataHandler == nil) {
        // 单例不需要release
        dataHandler = [[DataBaseHandler alloc] init];
        // 数据库可以一直保持被打开状态
        [dataHandler openDB];
        [dataHandler createTable];
    }
    
    
    return dataHandler;
    
}

// 结果的判断方法
- (void)judgeResult:(int)result name:(NSString *)name
{
    
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", name);
    } else {
        NSLog(@"%@失败, 错误代码:%d", name, result);
    }
}


- (void)openDB
{
    // 获取document文件夹路径
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 拼接一个数据库文件的存储路径
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"jokes.db"];
//    NSLog(@"aaaaaaaa%@", dbPath);
    
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    // 0代表成功
    
    [self judgeResult:result name:@"打开数据库"];
}

- (void)closeDB
{
    int result = sqlite3_close(dbPoint);
    [self judgeResult:result name:@"关闭数据库"];
}

- (void)createTable
{
    NSString *sqlStr = [NSString stringWithFormat:@"create table jokes (digest text, img text, upTimes text, title text primary key)"];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result name:@"创建表"];
    
}

- (void)insertJokes:(Jokes *)jokes
{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into jokes values('%@', '%@', '%@', '%@')", jokes.digest, jokes.img, jokes.upTimes, jokes.title];
    
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result name:@"插入数据"];
}


- (void)removeJokes:(Jokes *)jokes
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from jokes where title='%@'", jokes.title];
    int reslut = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:reslut name:@"删除数据"];
    NSLog(@"title%@", jokes.title);
}


- (NSMutableArray *)selectAll
{
    sqlite3_stmt *stmt = nil;
    // 把stmt指针指向本地数据, 获取一部分数据, 就是将sql语句指向的结果放入stmt中, 把有用的数据从stmt中提取出来
    int result = sqlite3_prepare_v2(dbPoint, "select * from jokes", -1, &stmt, NULL);
    
    NSMutableArray *arr = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 提取每一行的数据
            const unsigned char *digestChar = sqlite3_column_text(stmt, 0);
            NSString *digest = [NSString stringWithUTF8String:(const char *)digestChar];
            
            // 创建Jikes类来接收对象
            Jokes *jokes = [[Jokes alloc] init];
            jokes.digest = digest;
            
            const unsigned char *imgChar = sqlite3_column_text(stmt, 1);
            NSString *img = [NSString stringWithUTF8String:(const char *)imgChar];
            
            jokes.img = img;
            
            const unsigned char *upTimesChar = sqlite3_column_text(stmt, 2);
            NSString *upTimes = [NSString stringWithUTF8String:(const char *)upTimesChar];
            
            jokes.upTimes = upTimes;
            
            const unsigned char *titleChar = sqlite3_column_text(stmt, 3);
            NSString *title = [NSString stringWithUTF8String:(const char *)titleChar];
            
            jokes.title = title;
            
            [arr addObject:jokes];
            
            
            
            [jokes release];
        }
    }
    // 清空stmt中的数据
    sqlite3_finalize(stmt);
    
    return arr;
}





























@end
